/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Album;
import model.Song;

/**
 *
 * @author Jaice
 */
public class DaoAlbum {

    public ArrayList<Album> getAlbumBy() throws SQLException {
        ArrayList<Album> list = new ArrayList<Album>();
        Album album = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + " alb.ALBUM_ID, "
                + " alb.ANAME as albumname, "
                + " alb.RATING, "
                + " alb.DATE_RELEASED, "
                + " alb.PRICE, "
                + " alb.IMAGE_LOCATION "
                + " from ROOT.ALBUM alb "
                + " order by alb.ANAME ";

        PreparedStatement ps = conn.prepareStatement(query);

        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {
            album = new Album(rs.getInt("ALBUM_ID"),
                    rs.getString("AlbumNAME"),
                    rs.getDouble("RATING"),
                    rs.getDate("DATE_RELEASED"),
                    rs.getDouble("PRICE"),
                    rs.getString("IMAGE_LOCATION"));

            list.add(album);


        }


        return list;

    }

    public ArrayList<Album> retrieveGetAlbumBy() throws SQLException, ClassNotFoundException {
        return this.getAlbumBy();

    }

    public boolean addAlbum(Album album) throws SQLException {
        Connector connect = new Connector();
        Connection conn = connect.getConn();


        PreparedStatement ps = conn.prepareStatement("INSERT INTO ROOT.ALBUM ("
                + "ANAME, "
                + "RATING, "
                + "DATE_RELEASED, "
                + "PRICE, "
                + "IMAGE_LOCATION"
                + " ) "
                + "VALUES "
                + "(?,?,?,?,?)");

        ps.setString(1, album.getAlbumName());
        ps.setDouble(2, 0.0d);
        ps.setString(3, album.getDateReleasedS());
        ps.setDouble(4, album.getPrice());
        ps.setString(5, "/CaseStudy/images/album.jpg");



        return ps.executeUpdate() != 0 ? true : false;
    }

    public Album getAlbumById(String albumId) throws SQLException {
        Album album = null;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = "select "
                + " alb.ALBUM_ID, "
                + " alb.ANAME as albumname, "
                + " alb.RATING, "
                + " alb.DATE_RELEASED, "
                + " alb.PRICE, "
                + " alb.IMAGE_LOCATION "
                + " from ROOT.ALBUM alb "
                + " where alb.album_id = ?"
                + " order by alb.ANAME ";

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, albumId);

        ResultSet rs = ps.executeQuery();
//if was able to get a row
        rs.next();
        album = new Album(rs.getInt("ALBUM_ID"),
                rs.getString("AlbumNAME"),
                rs.getDouble("RATING"),
                rs.getDate("DATE_RELEASED"),
                rs.getDouble("PRICE"),
                rs.getString("IMAGE_LOCATION"));


        return album;

    }

    public boolean updateAlbum(Album album) throws SQLException {
        Connector connect = new Connector();
        Connection conn = connect.getConn();


        PreparedStatement ps = conn.prepareStatement("UPDATE ROOT.ALBUM "
                + " SET ANAME = ?, "
                + " RATING = ?, "
                + " DATE_RELEASED = ?, "
                + " PRICE = ? "
                + " WHERE ALBUM_ID = ?");

        ps.setString(1, album.getAlbumName());
        ps.setDouble(2, album.getPrice());
        ps.setString(3, album.getDateReleasedS());
        ps.setDouble(4, album.getPrice());
        ps.setInt(5, album.getAlbumId());



        return ps.executeUpdate() != 0 ? true : false;
    }
}
